﻿Imports System.Data.Odbc
Imports System.IO
Imports System.Xml
Imports System.Xml.Schema
Imports System.Net.Mime.MediaTypeNames
Imports System.Reflection
Module CstoneContactCreate

    Sub Main()
        Try
            Dim m_xmld As XmlDocument
            Dim m_nodelist As XmlNodeList
            Dim m_node As XmlNode

            'Create the XML Document
            m_xmld = New XmlDocument()
            'Load the Xml file
            m_xmld.Load("server.xml")
            'Get the list of name nodes 
            m_nodelist = m_xmld.SelectNodes("/server/info")
            'Loop through the nodes
            For Each m_node In m_nodelist
                'Get the firstName Element Value
                Dim dbconn = m_node.Item("dbconn").InnerXml

                'Dim days = m_node.Item("days").InnerXml

                Call BuildXMLData(dbconn)
            Next
        Catch errorVariable As Exception
            'Error trapping
            Console.Write(errorVariable.ToString())
        End Try

    End Sub
    Sub BuildXMLData(ByVal dbconn)
        Dim conn As OdbcConnection
        Dim comm As OdbcCommand
        Dim dr As OdbcDataReader
        Dim connectionString As String
        Dim sql As String
        Dim xml_doc As New XmlDocument()


        connectionString = dbconn
        sql = "select cl.clientid, firstname + ' ' + lastname as name, " & _
        "m_address1, m_address2, m_city, m_state, m_postalcode, cl.note, " & _
        "email_address, phone, lastname + ', ' + firstname as fileasname, firstname, lastname from client cl left outer join cliphone cli on cl.clientid " & _
        "= cli.clientid and sequence = 1"

        conn = New OdbcConnection(connectionString)
        conn.Open()
        comm = New OdbcCommand(sql, conn)

        dr = comm.ExecuteReader()

        Dim dec As XmlDeclaration = xml_doc.CreateXmlDeclaration("1.0", Nothing, Nothing)
        xml_doc.AppendChild(dec)
        Dim DocRoot As XmlElement = xml_doc.CreateElement("contacts")
        xml_doc.AppendChild(DocRoot)

        While (dr.Read())
            Dim ContactRoot As XmlElement = xml_doc.CreateElement("contact")
            Dim ContactidRoot As XmlElement = xml_doc.CreateElement("contact_id")
            Dim FNameRoot As XmlElement = xml_doc.CreateElement("full_name")
            Dim PhoneRoot As XmlElement = xml_doc.CreateElement("other_phone")
            Dim StreetRoot As XmlElement = xml_doc.CreateElement("other_street")
            Dim Street2Root As XmlElement = xml_doc.CreateElement("other_street2")
            Dim CityRoot As XmlElement = xml_doc.CreateElement("other_city")
            Dim StateRoot As XmlElement = xml_doc.CreateElement("other_state")
            Dim ZipRoot As XmlElement = xml_doc.CreateElement("other_zip")
            Dim EmailRoot As XmlElement = xml_doc.CreateElement("other_email")
            Dim NoteRoot As XmlElement = xml_doc.CreateElement("notes")
            Dim FileasRoot As XmlElement = xml_doc.CreateElement("fileasname")
            Dim FirstNameRoot As XmlElement = xml_doc.CreateElement("firstname")
            Dim LastNameRoot As XmlElement = xml_doc.CreateElement("lastname")

            ContactidRoot.InnerText = dr.GetValue(0).ToString()
            ContactRoot.AppendChild(ContactidRoot)

            FNameRoot.InnerText = dr.GetValue(1).ToString()
            ContactRoot.AppendChild(FNameRoot)

            PhoneRoot.InnerText = dr.GetValue(9).ToString()
            ContactRoot.AppendChild(PhoneRoot)

            StreetRoot.InnerText = dr.GetValue(2).ToString()
            ContactRoot.AppendChild(StreetRoot)

            Street2Root.InnerText = dr.GetValue(3).ToString()
            ContactRoot.AppendChild(Street2Root)

            CityRoot.InnerText = dr.GetValue(4).ToString()
            ContactRoot.AppendChild(CityRoot)

            StateRoot.InnerText = dr.GetValue(5).ToString()
            ContactRoot.AppendChild(StateRoot)

            ZipRoot.InnerText = dr.GetValue(6).ToString()
            ContactRoot.AppendChild(ZipRoot)

            NoteRoot.InnerText = dr.GetValue(7).ToString()
            ContactRoot.AppendChild(NoteRoot)

            EmailRoot.InnerText = dr.GetValue(8).ToString()
            ContactRoot.AppendChild(EmailRoot)

            FileasRoot.InnerText = dr.GetValue(10).ToString()
            ContactRoot.AppendChild(FileasRoot)

            FirstNameRoot.InnerText = dr.GetValue(11).ToString()
            ContactRoot.AppendChild(FirstNameRoot)

            LastNameRoot.InnerText = dr.GetValue(12).ToString()
            ContactRoot.AppendChild(LastNameRoot)

            DocRoot.AppendChild(ContactRoot)

        End While
        xml_doc.Save("contacts.xml")
        conn.Close()
        dr.Close()
        comm.Dispose()
        conn.Dispose()

    End Sub

End Module
